package com.silky.common.service;


import com.silky.common.constant.IConstant;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.security.SecureRandom;
import java.sql.*;
import java.time.Instant;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class SqlService {
    private static final SecureRandom RANDOM = new SecureRandom();

    public List<Map<String, Object>> search(Connection connection, String sql, Map<String, Object> parameterMap) throws Exception {
        ResultSet results = null;
        List<Map<String, Object>> mapList = null;
        String orgSql = "";
        String questionSql = "";
        try {
            Map dataMap = parameterMap;
            orgSql = sql;
            log.info("=========orgSql=========");
            log.info(orgSql);

            PreparedStatement ps = connection.prepareStatement(orgSql);
            try {
                results = ps.executeQuery();
                ResultSetMetaData rsmd = results.getMetaData();
                Map<String, Object> map = null;
                mapList = new ArrayList<>();
                while (results.next()) {
                    map = new HashMap<String, Object>();

                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        map.put(rsmd.getColumnName(i), results.getString(rsmd.getColumnName(i)));
                    }
                    //循环执行子查询
//                    if(!CollectionUtils.isEmpty(childCollection)){
//                        for(CollectionModel model:childCollection) {
//                            map.put(model.getProperty(),search(connection,tplFileName,model.getSelectId(),replaceVarByMap(model.getParameter(),map)));
//                        }
//                    }
                    mapList.add(map);
                }
                return mapList;
            } finally {
                try {
                    if (results != null) {
                        results.close();
                    }

                    if (ps != null) {
                        ps.close();
                    }

                } catch (SQLException e) {
                    e.printStackTrace();
                    log.error("关闭报错", e);
                }
            }
        } catch (Exception e) {
            log.error("多条查询报错,sql:" + questionSql, e);
            throw e;
        }
    }

    /**
     * 转义正则特殊字符 （$()*+.[]?\^{},|）
     *
     * @param keyword
     * @return
     */
    public String escapeExprSpecialWord(String keyword) {
        if (!StringUtils.isEmpty(keyword)) {
            String[] fbsArr = {"\\", "$", "(", ")", "*", "+", ".", "[", "]", "?", "^", "{", "}", "|", "\r", "\n"};
            for (String key : fbsArr) {
                if (keyword.contains(key)) {
//                    if(!key.equals("\r") && !key.equals("\n")) {
                    keyword = keyword.replace(key, "\\" + key);
//                    }else{
//                        keyword = keyword.replace(key, "\\\\\\" + key);
//                    }
                }
            }
        }
        return keyword;
    }

    /**
     * 提取<#if ...>exp</#if>中的exp
     *
     * @param exp
     * @return
     */
    private String pickConditionExp(String exp) {
        String regEx = "(>)(.*?)(</#if>)";
        Pattern r = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(exp);
        while (m.find()) {
            //m.appendReplacement(sb,"?");
            if (m.groupCount() > 0) {
                return m.group(2);
            }
        }
        return "";
    }

    /**
     * 替换正则表达式关键字
     *
     * @param exp
     * @return
     */
    private String replaceExpKeyExp(String exp) {
        exp = exp.replaceAll("\\{", "\\\\{");
        exp = exp.replaceAll("\\}", "\\\\}");
        exp = exp.replaceAll("\\(", "\\\\(");
        exp = exp.replaceAll("\\)", "\\\\)");
        exp = exp.replaceAll("\\$", "\\\\\\$");
        return exp;
    }


    /**
     * 逻辑段计算  目前暂支持 (xxx??) 格式
     *
     * @param logicSeg
     * @param map
     * @return
     */
    private boolean calculateLogic(String logicSeg, Map map) throws Exception {
        int leftBracketsPos = logicSeg.indexOf("(");
        int rightBracketsPos = logicSeg.indexOf(")");
        int doubleQuestionPos = logicSeg.indexOf("??");

        if (leftBracketsPos == -1 || rightBracketsPos == -1 || doubleQuestionPos == -1)
            throw new Exception("无效的判断逻辑表达式:" + logicSeg);
        //提取属性名
        String propertyName = logicSeg.substring(leftBracketsPos + 1, doubleQuestionPos);
        if (!StringUtils.isEmpty(propertyName)) {
            if (map.containsKey(propertyName.trim())) {
                return true;
            }
        }
        return false;
    }

    /**
     * 替换变量字符串中变量
     *
     * @param str 原始字符串
     * @param map 包含变量值
     * @return
     */
    public String replaceVarByMap(String str, Map map) {
        String regEx = "(\\$\\{)(.*?)(\\})";
        Pattern r = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            if (m.groupCount() >= 3) {
                //System.out.println(m.group(2));
                m.appendReplacement(sb, (String) map.get(m.group(2)));
            }
        }
        m.appendTail(sb);
        return sb.toString();
    }

    /**
     * 替换为带？的sql
     *
     * @param sql 原始sql
     * @return
     */
    public String getReplaceVarToQuestion(String sql) {
        String regEx = "(\\$\\{)(.*?)(\\})";
        Pattern r = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(sql);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            m.appendReplacement(sb, "?");
        }
        m.appendTail(sb);
        return sb.toString();
    }

    /**
     * 获取sql变量key列表
     *
     * @param orgSql
     * @return
     */
    public List<String> getVarKeys(String orgSql) {
        String regEx = "(\\$\\{)(.*?)(\\})";
        Pattern r = Pattern.compile(regEx, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        // Now create matcher object.
        Matcher m = r.matcher(orgSql);
        List<String> replaceGroups = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        List<String> retKeys = new ArrayList<>();
        while (m.find()) {
            m.appendReplacement(sb, "?");
            if (m.groupCount() >= 3) {
                //System.out.println(m.group(2));
                retKeys.add("${"+m.group(2)+"}");
            }
        }
        return retKeys;
    }

    /**
     * 执行插入,更新，删除
     *
     * @param conn
     * @param sql
     * @param parameterMap
     * @return
     * @throws SQLException
     */
    public int doUpdate(Connection conn, String sql, Map<String, Object> parameterMap) throws Exception {
        PreparedStatement ps = null;
        String debugSql="";
        try {
            String questionSql = getReplaceVarToQuestion(sql);
            log.info("=========questionSql=========");
            log.info(questionSql);
            debugSql = questionSql;
            List<String> keysList = getVarKeys(sql);
            ps = conn.prepareStatement(questionSql);
            for (int i = 0; i < keysList.size(); i++) {
                //如果是随机函数，则生成一个随机数
                if (keysList.get(i).toLowerCase().startsWith("${" + IConstant.RANDOM)) {
                    String randValue = getRandValue(keysList.get(i));
                    ps.setObject(i + 1, randValue);
//                    debugSql = debugSql.replaceFirst("\\?", randValue);
                } else {
                    Object val = parameterMap.get(keysList.get(i));
                    //debugSql = debugSql.replaceFirst("\\?", val == null ? "null" : val.toString());
                    ps.setObject(i + 1, val);
                }
            }


            // 执行插入
            int rowsAffected = ps.executeUpdate();

            // 打印受影响的行数
//            System.out.println(debugSql);
            return rowsAffected;
        }catch(SQLException e1){
            log.error("执行sql失败:"+debugSql, e1);
            throw e1;
        } catch (Exception e) {
            log.error("执行sql失败:"+debugSql, e);
            throw e;
        } finally {
            // 关闭资源
            if (ps != null) {
                ps.close();
            }
        }
    }

    /**
     * 执行批量插入,更新，删除
     *
     * @param conn
     * @param sql
     * @param parameterMaps
     * @return
     * @throws SQLException
     */
    public int doBatchUpdate(Connection conn, String sql, List<Map<String, Object>> parameterMaps) throws Exception {
        PreparedStatement ps = null;
        String debugSql="";
        try {
            String questionSql = getReplaceVarToQuestion(sql);
            log.info("=========questionSql=========");
            log.info(questionSql);
            debugSql = questionSql;
            conn.setAutoCommit(false);
            List<String> keysList = getVarKeys(sql);
            ps = conn.prepareStatement(questionSql);
            int rowsAffected =0;
            for(Map<String, Object> parameterMap : parameterMaps) {
                for (int i = 0; i < keysList.size(); i++) {
                    //如果是随机函数，则生成一个随机数
                    if (keysList.get(i).toLowerCase().startsWith("${" + IConstant.RANDOM)) {
                        String randValue = getRandValue(keysList.get(i));
                        ps.setObject(i + 1, randValue);
                    debugSql = debugSql.replaceFirst("\\?", randValue);
                    } else {
                        Object val = parameterMap.get(keysList.get(i));
                        debugSql = debugSql.replaceFirst("\\?", val == null ? "null" : val.toString());
                        ps.setObject(i + 1, val);
                    }
                }
                ps.addBatch();
            }
            // 执行插入
//            rowsAffected += ps.executeUpdate();
            int[] result =ps.executeBatch();
            for(int i:result){
                 rowsAffected +=i;
             }
            // 打印受影响的行数
            System.out.println(debugSql);
            conn.commit();
            return rowsAffected;
        }catch(SQLException e1){
            log.error("执行sql失败:"+debugSql, e1);
            conn.rollback();
            throw e1;
        } catch (Exception e) {
            log.error("执行sql失败:"+debugSql, e);
            conn.rollback();
            throw e;
        } finally {
            // 关闭资源
            if (ps != null) {
                ps.close();
            }
        }
    }
    /**
     * 获取随机值
     * @param randomExp
     * @return
     */
    private String getRandValue(String randomExp) throws Exception {
        //提取两个括号之间字符串
        int spos = randomExp.indexOf("(");
        int epos = randomExp.indexOf(")");
        if(spos==-1 ||epos==-1||spos>epos)
            throw new Exception("无效的随机函数表达式："+randomExp);
        String valueExp = randomExp.substring(spos+1,epos);
        int number;
        try {
            number = Integer.valueOf(valueExp);
            if(number<=0)
                throw new Exception("无效的随机函数表达式："+randomExp+",数字必须大于0！");
            // 获取当前时间的纳秒部分
            long nanoTime = System.currentTimeMillis();
            // 将纳秒转换为9位字符串，不足9位前面补0
            String nanoStr = String.format("%09d", nanoTime);
            // 生成一个0到9999之间的随机数，转换为4位字符串，不足4位前面补0
            int randomNum = RANDOM.nextInt(10000);
            String randomStr = String.format("%04d", randomNum);
            // 拼接成13位的字符串
            String uniqueId = nanoStr + randomStr;
            // 如果需要number位，可以再添加额外的随机数
            while(uniqueId.length() < number) {
                int extraRandomNum = RANDOM.nextInt(100000);
                uniqueId += String.format("%05d", extraRandomNum);
            }
            return uniqueId.substring(0, number); // 确保结果为19位
        }catch (NumberFormatException e){
            throw new Exception("无效的随机函数表达式："+randomExp+",括号内必须为数字！");
        }

    }
}
